package com.jiuqi.common.authority.netrep.origin;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.jiuqi.common.authority.netrep.origin.util.DBConnect;
import com.jiuqi.common.authority.netrep.origin.util.JqLib;
import com.jiuqi.common.authority.netrep.origin.util.OrderGenerator;

public class UserChildren {
  protected DBConnect dbConnect=null;

  public UserChildren(DBConnect conn){
    super();
    dbConnect=conn;
  }

  public boolean addUserChild(String user, String child, String password) throws AuthorityException {
    if(existUserChild(user, child)) return false;
    try{
      Connection conn=dbConnect.getConnection();
      try{
        PreparedStatement ps=conn.prepareStatement(SQL_NEW_LINK);
        try{
          ps.setString(1,user);
          ps.setString(2,child);
          ps.setString(3,JqLib.encodePassword(password, Consts.MAX_PASSWORD));
          ps.setString(4,OrderGenerator.newOrder());
          return ps.executeUpdate()>0;
        }finally{
          ps.close();
        }
      }finally{
        dbConnect.releaseConnection();
      }
    }catch(SQLException ex){
      throw new AuthorityException(ex);
    }
  }
  public boolean delUserChild(String user, String child) throws AuthorityException {
    try{
      Connection conn=dbConnect.getConnection();
      try{
        PreparedStatement ps=conn.prepareStatement(SQL_DELETE_LINK);
        try{
          ps.setString(1,user);
          ps.setString(2,child);
          return ps.executeUpdate()>0;
        }finally{
          ps.close();
        }
      }finally{
        dbConnect.releaseConnection();
      }
    }catch(SQLException ex){
      throw new AuthorityException(ex);
    }
  }
  public ArrayList getUserChildList(String user) throws AuthorityException {
    ArrayList result = new ArrayList();
    try{
      Connection conn=dbConnect.getConnection();
      try{
        PreparedStatement ps=conn.prepareStatement(SQL_GET_LINKS);
        try{
          ps.setString(1,user);
          ResultSet rs=ps.executeQuery();
          try {
            while (rs.next())
              result.add(rs.getString(1));
          }finally{
            rs.close();
          }
        }finally{
          ps.close();
        }
      }finally{
        dbConnect.releaseConnection();
      }
    }catch(SQLException ex){
      throw new AuthorityException(ex);
    }
    return result;
  }
  public String getUserChildPassword(String user, String child) throws AuthorityException {
    String result = null;
    try{
      Connection conn=dbConnect.getConnection();
      try{
        PreparedStatement ps=conn.prepareStatement(SQL_EXIST_LINK);
        try{
          ps.setString(1,user);
          ps.setString(2,child);
          ResultSet rs=ps.executeQuery();
          try {
            if (rs.next()) {
              result = JqLib.decodePassword(rs.getString(1));
              if (result == null) result = "";
            }
          }finally{
            rs.close();
          }
        }finally{
          ps.close();
        }
      }finally{
        dbConnect.releaseConnection();
      }
    }catch(SQLException ex){
      throw new AuthorityException(ex);
    }
    return result;
  }

  public boolean existUserChild(String user, String child) throws AuthorityException {
    boolean result=false;
    try{
      Connection conn=dbConnect.getConnection();
      try{
        PreparedStatement ps=conn.prepareStatement(SQL_EXIST_LINK);
        try{
          ps.setString(1,user);
          ps.setString(2,child);
          ResultSet rs=ps.executeQuery();
          result=rs.next();
          rs.close();
        }finally{
          ps.close();
        }
      }finally{
        dbConnect.releaseConnection();
      }
    }catch(SQLException ex){
      throw new AuthorityException(ex);
    }
    return result;
  }

  public boolean exchangeUserChildOrder(String user, String child1, String child2) throws AuthorityException {
    boolean result = false;
    try{
      Connection conn=dbConnect.getConnection();
      try{
        String order1 = "", order2 = "";
        PreparedStatement stmt1 = conn.prepareStatement(SQL_LOAD_LINK_ORDER);
        try {
          stmt1.setString(1, user);
          stmt1.setString(2, child1);
          stmt1.setString(3, child2);
          ResultSet rs1 = stmt1.executeQuery();
          try{
            if(rs1.next()){
              child1 = rs1.getString(1);
              order1 = rs1.getString(2);
            }
            if(rs1.next()){
              child2 = rs1.getString(1);
              order2 = rs1.getString(2);
            }
          } finally {
            rs1.close();
          }
        } finally {
          stmt1.close();
        }
        PreparedStatement stmt2 = conn.prepareStatement(SQL_UPDATE_LINK_ORDER);
        try {
          stmt2.setString(1, order2);
          stmt2.setString(2, user);
          stmt2.setString(3, child1);
          result = stmt2.executeUpdate() > 0;
          stmt2.setString(1, order1);
          stmt2.setString(2, user);
          stmt2.setString(3, child2);
          result = result  && stmt2.executeUpdate() > 0;
        } finally {
          stmt2.close();
        }
      }finally{
        dbConnect.releaseConnection();
      }
    }catch(SQLException ex){
      throw new AuthorityException(ex);
    }
    return result;
  }

  private static final String SQL_NEW_LINK=
      "INSERT INTO sys_userchildren (uc_user,uc_child,uc_verify,sys_order) VALUES (?,?,?,?)";
  private static final String SQL_DELETE_LINK=
      "DELETE FROM sys_userchildren WHERE (uc_user=?) AND (uc_child=?)";
  private static final String SQL_GET_LINKS=
      "SELECT uc_child FROM sys_userchildren WHERE (uc_user=?) ORDER BY sys_order";
  private static final String SQL_EXIST_LINK=
      "SELECT uc_verify FROM sys_userchildren WHERE (uc_user=?) AND (uc_child=?)";

  private static final String SQL_LOAD_LINK_ORDER =
      "SELECT uc_child,sys_order FROM sys_userchildren WHERE (uc_user=?) AND (uc_child=? OR uc_child=?)";
  private static final String SQL_UPDATE_LINK_ORDER =
      "UPDATE sys_userchildren SET sys_order=? WHERE uc_user=? AND uc_child=?";
}